<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/5/20
 * Time: 8:37
 */

namespace app\common\logic;


use think\Db;

class outExcel
{
    static protected $statusCode = [
        0  => '筹备中',
        1  => '待开发',
        2  => '开发中',
        3  => '待测试',
        4  => '测试中',
        5  => '待上线',
        6  => '已上线'
    ];
    //static protected $need = ['姓名','岗位','功能名称','用时','需求状态','需求类型','所属项目','所属产品','所属版本','提出日期'];
    static protected $needField = ['uname','deptid','name','manhour','status','type','pname','product_id','vname','ptime'];
    static protected $bugField  = ['uname','deptid','title','degree','status','type','pname','product_id','vname','creator','ctime'];

    /**
     * @title 导出工时统计数据
     * @description 接口说明
     * @author jyh
     * @url /home/statistics/getStatLst
     * @method POST
     */
    public static function exportExcel($map = [])
    {
        $needModel = new \app\home\model\ProjectNeed();

        $where = [];
        $like = '';
        $where['pn.status'] = ['<>',0];
        $where['rp.status'] = ['<>',0];
        $where['rp.newPlan'] = ['=',1];
        foreach ($map as $key => $value) {
            if ($value == '') unset($map[$key]);
        }
        if (isset($map['user_id'])) $where['rp.user_id'] = $map['user_id'];
        if (isset($map['project_id'])) $where['pn.project_id'] = (int) $map['project_id'];
        if (isset($map['status'])) $where['pn.status'] = ['in',$map['status']];
        if (isset($map['time'])) {
            $where['rp.ctime'][] = ['>=',strtotime(date('Y-m-d',$map['time'][0]) . ' 00:00:00')];
            $where['rp.ctime'][] = ['<=',strtotime(date('Y-m-d',$map['time'][1]) . ' 23:59:59')];
        } else {
            $where['rp.ctime'][] = ['>=',strtotime(date('Y-m-d',strtotime('-6 day')) . ' 00:00:00')];
            $where['rp.ctime'][] = ['<=',strtotime(date('Y-m-d',time()) . ' 23:59:59')];
        }
        if (isset($map['product_id'])) {
            $where['pn.product_id'] = $map['product_id'];
            if (isset($map['versions_id'])) {
                $where['pn.versions_id'] = $map['versions_id'];
            }
        }
        if (isset($map['keywords'])) {
            $like = "pn.name like '%{$map['keywords']}%' OR u.name like '%{$map['keywords']}%' OR p.name like '%{$map['keywords']}%' OR pp.name like '%{$map['keywords']}%'";
            switch ($map['keywords']) {
                case '前端';
                    $deptid = 0;
                    break;
                case '后端';
                    $deptid = 1;
                    break;
                case '产品经理';
                    $deptid = 2;
                    break;
                case '测试';
                    $deptid = 3;
                    break;
                case '运维';
                    $deptid = 4;
                    break;
                case '管理员';
                    $deptid = 5;
                    break;
            }
            if (isset($deptid)) $like .= " OR u.deptid like '%{$deptid}%'";
        }
        $data = $needModel
            ->alias('pn')
            ->join('Project p','pn.project_id = p.id and p.status=0 and p.deleted is null','left')
            ->join('Project pp','pn.product_id = pp.id and pp.status=5 and p.deleted is null','left')
            ->join('RecordPlan rp','pn.`id` = rp.`need_id` and rp.status=1','left')
            ->join('User u','u.`id` = rp.`user_id`','left')
            ->join('Versions v','pn.`versions_id` = v.`id`','left')
            ->where($where)
            ->where('pn.deleted','null')
            ->where($like)
            ->field("CASE
                WHEN pn.`front_user` = rp.`user_id` THEN
                 FORMAT(SUM(CASE pn.`front_adjust` WHEN '0.0' THEN pn.`front_predict` ELSE pn.`front_adjust` END * pn.`front_plan`/100),1)
                WHEN pn.`after_user` = rp.`user_id` THEN
                 FORMAT(SUM(CASE pn.`after_adjust` WHEN '0.0' THEN pn.`after_predict` ELSE pn.`after_adjust` END * pn.`after_plan`/100),1)
                ELSE
                 0
                END AS `manhour`,pn.`id`,rp.`user_id`,u.`name` as `uname`,pn.`name`,u.`deptid`,pn.`status`,pn.`type`,p.`name` as `pname`,pn.`product_id`,v.`name` as `vname`,rp.`ctime` ptime,pn.`status`,pn.`block_id`")
            ->group('rp.`ctime`')
            ->order('pn.ptime','desc')
            ->select();

        $dealData = [];
        $i = 1;
        foreach ($data as $key => $value) {
            foreach (self::$needField as $k => $v) {
                $dealData[$key][$v] = $value[$v];
                $dealData[$key]['sort_id'] = $i;
            }
            $i++;
        }
        return $dealData;
    }


    /**
     * @title 导出Bug统计数据
     * @description 接口说明
     * @author jyh
     * @url /home/download/exportBugExcel
     * @method POST
     */
    public static function exportBugExcel($map = [])
    {
        $bugModel = new \app\home\model\ProjectBug();
        $where = [];
        $like = '';
        foreach ($map as $key => $value) {
            if ($value === '') unset($map[$key]);
        }
        if (isset($map['time'])) {
            $where['pb.ctime'][] = ['>=',strtotime(date('Y-m-d',$map['time'][0]) . ' 00:00:00')];
            $where['pb.ctime'][] = ['<=',strtotime(date('Y-m-d',$map['time'][1]) . ' 23:59:59')];
        } else {
            $where['pb.ctime'][] = ['>=',strtotime(date('Y-m-d',strtotime('-6 day')) . ' 00:00:00')];
            $where['pb.ctime'][] = ['<=',strtotime(date('Y-m-d',time()) . ' 23:59:59')];
        }
        if (isset($map['product_id'])) {
            $where['pb.product_id'] = $map['product_id'];
            if (isset($map['versions_id'])) {
                $where['pb.versions_id'] = $map['versions_id'];
            }
        }
        if (isset($map['user_id'])) $where['pb.assigner'] = $map['user_id'];
        if (isset($map['project_id'])) $where['pb.project_id'] = $map['project_id'];
        if (isset($map['status'])) $where['pb.status'] = ['in',$map['status']];
        if (isset($map['degree'])) $where['pb.degree'] = $map['degree'];
        if (isset($map['keywords'])) {
            $like = "pb.title like '%{$map['keywords']}%' OR u.name like '%{$map['keywords']}%' OR p.name like '%{$map['keywords']}%' OR pp.name like '%{$map['keywords']}%'";
            switch ($map['keywords']) {
                case '前端';
                    $deptid = 0;
                    break;
                case '后端';
                    $deptid = 1;
                    break;
                case '产品经理';
                    $deptid = 2;
                    break;
                case '测试';
                    $deptid = 3;
                    break;
                case '运维';
                    $deptid = 4;
                    break;
                case '管理员';
                    $deptid = 5;
                    break;
            }
            if (isset($deptid)) $like .= " OR u.deptid like '%{$deptid}%'";

        }

        $data = $bugModel
            ->alias('pb')
            ->join('Project p','pb.project_id = p.id and p.status=0 and p.deleted is null','left')
            ->join('Project pp','pb.product_id = pp.id and pp.status=5 and p.deleted is null','left')
            ->join('Versions v','pb.`versions_id` = v.`id`','left')
            ->join('User u','u.`id` = pb.`assigner`','left')
            ->where($where)
            ->where('pb.deleted','null')
            ->where($like)
            ->field("pb.`id`,u.`name` as `uname`,pb.`title`,u.`deptid`,pb.`status`,pb.`degree`,pb.`type`,pb.`creator`,p.`name` as `pname`,pp.`id` product_id,v.`name` as `vname`,pb.`ctime`,pb.`status`")
            ->group('pb.`ctime`')
            ->order('pb.ctime','desc')
            ->select();

        $dealData = [];
        $i = 1;
        foreach ($data as $key => $value) {
            foreach (self::$bugField as $k => $v) {
                $dealData[$key][$v] = $value[$v];
                $dealData[$key]['sort_id'] = $i;
            }
            $i++;
        }
        return $dealData;
    }

    public static function exportProjectExcel($map = [])
    {
        // 有$map条件 就是筛选
        $where = [];
        $like = '';
        $issue_id = isset($map['issue_id']) && $map['issue_id'] == '' ? true : false;
        foreach ($map as $key => $value) {
            if ($value === '') unset($map[$key]);
        }
        $where['task_allot'] = ['in',[0,4]];
        if (isset($map['project_id'])) {
            $where['pn.project_id'] =  $map['project_id'];
            if (isset($map['issue_id'])) $where['pn.issue_id'] = $map['issue_id'];
        } else if (isset($map['product_id'])) {
            $where['pn.product_id'] = $map['product_id'];
            if (isset($map['versions_id'])) $where['pn.versions_id'] =  $map['versions_id'];
        } else {
            $where['pn.issue_id'] = isset($map['issue_id']) ? $map['issue_id'] : Db::name('IssueVersions')->order('ctime','desc')->value('id');
            if ($issue_id) unset($where['pn.issue_id']);
        }
        if (isset($map['status'])) $where['pn.status'] =  $map['status'];
        if (isset($map['issue_id'])) $where['pn.issue_id'] = $map['issue_id'];
        if (isset($map['type'])) $where['pn.type'] = $map['type'];
        if (isset($map['time'])) {
            $where['pn.etime'][] = ['>=',strtotime(date('Y-m-d',$map['time'][0]) . ' 00:00:00')];
            $where['pn.etime'][] = ['<=',strtotime(date('Y-m-d',$map['time'][1]) . ' 23:59:59')];
        }
        if (isset($map['front_user'])) $where['pn.front_user'] = $map['front_user'];
        if (isset($map['after_user'])) $where['pn.after_user'] = $map['after_user'];
        if (isset($map['keywords'])) $like =  "pn.id like '%{$map['keywords']}%' OR pn.name like '%{$map['keywords']}%' OR pn.describe like '%{$map['keywords']}%' OR p.name like '%{$map['keywords']}%'";
        $field = 'pn.`id`,pn.`status`,pn.`issue_id`,pn.`type`,pn.`priority`,pn.`front_user`,pn.`after_user`,pn.`product_id`,pn.`source`,pn.`describe`,pn.`creator`,pn.`name`,pn.`ctime` as `ptime`,pn.`front_predict`,pn.`after_predict`,pn.`front_plan`,pn.`after_plan`,pn.`isup`,pn.`front_adjust`,pn.`after_adjust`,pn.`block_id`,pn.`etime`';
        $data = Db::name('project_need')
            ->alias('pn')
            ->join('Project p','pn.product_id = p.id','left')
            ->where($where)
            ->where('pn.deleted','null')
            ->where($like)
            ->field($field)
            ->order('pn.ctime', 'desc')
            ->select();

        $projectName = Db::name('project')->column('name','id');
        $block = Db::name('block')->where('deleted',null)->column('name','id');
        $deploy = Db::name('deploy')->where('deleted',null)->column('name','id');
        $user = Db::name('user')->where('deleted',0)->column('name','id');
        //dump($data);die;
        $i = 1;
        foreach ($data as $key => $value) {
            $data[$key]['status'] = self::$statusCode[$value['status']];
            $data[$key]['product_id'] = $projectName[$value['product_id']];
            $data[$key]['block_id'] = $block[$value['block_id']];
            $data[$key]['type'] = $deploy[$value['type']];
            $data[$key]['priority'] = $deploy[$value['priority']];
            $data[$key]['creator'] = $user[$value['creator']];
            $data[$key]['front_user'] = $value['front_user'] ? $user[$value['front_user']] : '';
            $data[$key]['after_user'] = $value['after_user'] ? $user[$value['after_user']] : '';
            $data[$key]['ptime'] = $value['ptime'] ? date('Y-m-d',$value['ptime']) : '';
            $data[$key]['etime'] = $value['etime'] ? date('Y-m-d',$value['etime']) : '';
            $data[$key]['sort_id'] = $i;
            $i++;
        }

        return $data;
    }
}